﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using MySql.Data.MySqlClient;



namespace LibraryETL
{
    class Library2StagingArea
    {
        private readonly Dictionary<int, BookDIM> books;
        private readonly Dictionary<int, StudentDIM> students;
        private readonly List<BorrowingsFCT> borrowings;
        private readonly Dictionary<string, TimeDIM> times;
        private int libraryKEY = -1;


        internal Library2StagingArea()
        {
            books = new Dictionary<int, BookDIM>();
            students = new Dictionary<int, StudentDIM>();
            borrowings = new List<BorrowingsFCT>();
            times = new Dictionary<string, TimeDIM>();
        }


        /// <summary>
        /// Clear Staging Area tables used by this ETL
        /// </summary>
        internal void Initialize()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "TRUNCATE TABLE lib_borrowingFCT;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_libraryAccount;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_book;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_student;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_time;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_library;";
            command.ExecuteNonQuery();
            command.CommandText = "TRUNCATE TABLE lib_faculty;";
            command.ExecuteNonQuery();
            Console.WriteLine("Staging area data cleared.\n==========================");
        }


        internal void Extract()
        {
            try
            {
                downloadBooks();
/*                foreach (BookDIM book in books.Values)
                    Console.WriteLine("BOOK: " + book.Title + " by " + book.Authors);*/
                downloadStudents();
/*                foreach(StudentDIM student in students.Values)
                    Console.WriteLine(student.FirstName + " " +student.LastName);*/
                downloadBorrowings();
/*                foreach(BorrowingsFCT borrowing in borrowings)
                    Console.WriteLine(borrowing.StudentFK + ", " + borrowing.BookFK + ", ::::" + borrowing.StartTimeKEY + " - " + borrowing.EndTimeKEY);*/

            }
            catch (SqlException sqle)
            {
                Console.Error.WriteLine("A database related error occured. " + sqle.Message);
            }
        }


        /// <summary>
        /// Transform + Load phases
        /// </summary>
        internal void Load()
        {
            saveFacultyAndLibrary();
            Console.WriteLine("Faculties and libraries saved...");
            saveTimes();
            Console.WriteLine("Time-stamps saved...");
            saveBooks();
            Console.WriteLine("Publications saved...");
            saveStudentsAndLibAccounts();
            Console.WriteLine("Studends and thier library accounts saved...");
            saveBorrowings();
            Console.WriteLine("Borrowings saved...\n\n");
        }


        private void downloadBooks()
        {
            SqlConnection connection = getLibraryConnection();

            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select d.ID_dielo, d.nazov, o.meno + ' ' + o.priezvisko as author from dielo d " +
                                  " left join osoba_dielo od on (d.ID_dielo = od.FK_dielo) " +
                                  " left join osoba o on (od.FK_osoba = o.ID_osoba) " +
                                  " order by d.ID_dielo";
            SqlDataReader reader = command.ExecuteReader();
            int lastID_dielo = -1;
            BookDIM lastBook = null;
            while(reader.Read())
            {
                int ID_dielo = reader.GetInt32(0);
                if(ID_dielo == lastID_dielo)                //same book, next author
                    lastBook.AddAuthor(reader.GetString(2));
                else
                {
                    lastID_dielo = ID_dielo;
                    lastBook = new BookDIM(ID_dielo, reader.GetString(1), reader.IsDBNull(2) ? string.Empty
                                                                                             : reader.GetString(2));
                    books.Add(ID_dielo, lastBook);
                }
            }

            connection.Close();
        }


        private void downloadStudents()
        {
            SqlConnection connection = getLibraryConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select * from osoba "; // "where rocnik IS NOT NULL and program IS NOT NULL";
            SqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
            {
                int id = reader.GetInt32(0);
                string ais = reader.IsDBNull(5) ? "-1" : reader.GetString(5);
                StudentDIM student = new StudentDIM(id, ais, reader.GetString(1), reader.GetString(2),
                                                    reader.IsDBNull(3) ? null : new int?(int.Parse(reader.GetString(3))),
                                                    reader.IsDBNull(4) ? string.Empty : reader.GetString(4));
                students.Add(id, student);
            }

            connection.Close();
        }


        private void downloadBorrowings()
        {
            SqlConnection connection = getLibraryConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select FK_dielo, FK_osoba, datum_vypozicania, termin_vratenia from vypozicka";
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                TimeDIM startTime = new TimeDIM(reader.GetDateTime(2));
                TimeDIM endTime = new TimeDIM(reader.GetDateTime(3));
                addTime(startTime);
                addTime(endTime);
                int studentID = reader.GetInt32(1);
                BorrowingsFCT borrowing = new BorrowingsFCT(studentID, reader.GetInt32(0), startTime.TimeKEY, endTime.TimeKEY);
                borrowings.Add(borrowing);
            }

            connection.Close();
        }



        private void saveFacultyAndLibrary()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "insert into lib_faculty(name, address) values "+
                                  "('Faculty of informatics and information technolgies', 'Ilkovicova 3, Bratislava');";
            command.ExecuteNonQuery();
            int newID = getLastID(connection);

            command.CommandText = "insert into lib_library(facultyKEY, name) values "+
                                  "(" + newID + ", 'Kniznica FEI')";
            libraryKEY = getLastID(connection);
            command.ExecuteNonQuery();

            connection.Close();
        }

        private void saveTimes()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (TimeDIM time in times.Values)
            {
                command.CommandText = "insert into lib_time values ('" + time.TimeKEY + "', '" + time.Date + "', '" + time.DayTime + "');";
                command.ExecuteNonQuery();
            }

            connection.Close();
        }


        private void saveBooks()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach(BookDIM book in books.Values)
            {
                command.CommandText = "insert into lib_book(title, authors) values ('"+book.Title+"', '"+book.Authors+"');";
                command.ExecuteNonQuery();
                int newID = getLastID(connection);
                book.SurrogateKEY = newID;
            }
            
            connection.Close();
        }


        private void saveStudentsAndLibAccounts()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (StudentDIM student in students.Values)
            {
                command.CommandText = "insert into lib_student(ais_id, name, surname, grade, fieldOfStudy) values (" + student.AIS_id +
                                      ", '" + student.FirstName + "', '" + student.LastName +
                                      "', " + (student.Grade.HasValue ? student.Grade.Value.ToString() : "NULL") +
                                      ", '" + student.FieldOfStudy + "');";
                command.ExecuteNonQuery();
                int newID = getLastID(connection);
                student.SurrogateKEY = newID;

                LibraryAccountDIM account = student.GetStudentLibraryAccount();
                command.CommandText = "insert into lib_libraryAccount(studentKEY, creationTimeKEY, active) values (" +
                                       newID + ", " + (account.CreationTimeKEY!=null ? "'"+account.CreationTimeKEY+"'" : "NULL") +
                                       ", " + (account.Active ? 1 : 0) + ")";
                command.ExecuteNonQuery();
                int newLAID = getLastID(connection);
                student.LibAccountSurrogateKEY = newLAID;
            }

            connection.Close();
        }


        private void saveBorrowings()
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            foreach (BorrowingsFCT borr in borrowings)
            {
                borr.LibrarySurrogateKEY = libraryKEY;
                borr.LibraryAccountSurrogateKEY = students[borr.StudentFK].LibAccountSurrogateKEY;
                borr.BookSurrogateKEY = books[borr.BookFK].SurrogateKEY;
                command.CommandText = "insert into lib_borrowingFCT(libraryAccountKEY, startTimeKEY, endTimeKey, libraryKEY, bookKEY) values ("
                                      + borr.LibraryAccountSurrogateKEY + ", '" + borr.StartTimeKEY + "', '" + borr.EndTimeKEY + "', " +
                                      borr.LibrarySurrogateKEY + ", " + borr.BookSurrogateKEY + ");";
                command.ExecuteNonQuery();
            }

            connection.Close();
        }


        /// <returns>TRUE on success adition</returns>
        private bool addTime(TimeDIM time)
        {
            if(times.ContainsKey(time.TimeKEY))
                return false;
            times.Add(time.TimeKEY, time);
            return true;
        }



/************************************** Database helper methods **************************************/
/*****************************************************************************************************/
        /// <summary>
        /// Get open connection to the source system of Library. Remember to close it.
        /// </summary>
        /// <returns></returns>
        private static SqlConnection getLibraryConnection()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["MSSqlConnectionString"].ConnectionString;
            connection.Open();
            return connection;
        }


        /// <summary>
        /// Get open connection to the StagingArea. Remember to close it.
        /// </summary>
        /// <returns></returns>
        private static MySqlConnection getSAConnection()
        {
            string myConString = ConfigurationManager.ConnectionStrings["StagingAreaConnectionString"].ConnectionString;
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            return connection;
        }


        /// <summary>
        /// Get last generated auto-increment ID. Applies for last INSERT on any table.
        /// </summary>
        /// <param name="conn">Open connection to MySQL server. Will not be closed in this method.</param>
        private static int getLastID(MySqlConnection conn)
        {
            if(conn.State != ConnectionState.Open)
                throw new ArgumentException("The given connection is in unexpected state (should be open)");
            int result = -1;

            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "SELECT LAST_INSERT_ID()";
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
                result = reader.GetInt32(0);
            reader.Close();
            return result;
        }
    }
}
